﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class PodDetailDA : IPodDetailDA
    {
        private string ACCEPTED_QTY = "@ACCEPTED_QTY";
        private string BOL_LINE_ID = "@BOL_LINE_ID";
        private string BREAKAGE_QTY = "@BREAKAGE_QTY";
        private string CREATED_BY = "@CREATED_BY";
        private string DELIVERY_ID = "@DELIVERY_ID";
        private string DELIVERY_LINE_ID = "@DELIVERY_LINE_ID";
        private string LINE_ID = "@LINE_ID";
        private string LOSE_QTY = "@LOSE_QTY";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PKG_BREAKAGE_QTY = "@PKG_BREAKAGE_QTY";
        private string POD_ID = "@POD_ID";
        private string QTY = "@QTY";
        private string REJECTED_QTY = "@REJECTED_QTY";
        private string REMARK = "@REMARK";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_POD_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@POD_DETAIL WHERE POD_ID = @POD_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_POD_DETAIL = " DELETE FROM @_@POD_DETAIL WHERE POD_ID = @POD_ID  ";
        private string SQL_INSERT_POD_DETAIL = " INSERT INTO @_@POD_DETAIL ( WH_ID, POD_ID, LINE_ID, BOL_LINE_ID, DELIVERY_ID, DELIVERY_LINE_ID, STATUS, OWNER_ID, SKU_ID, PACK_ID, QTY, ACCEPTED_QTY, REJECTED_QTY, LOSE_QTY, BREAKAGE_QTY, PKG_BREAKAGE_QTY, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @POD_ID, @LINE_ID, @BOL_LINE_ID, @DELIVERY_ID, @DELIVERY_LINE_ID, @STATUS, @OWNER_ID, @SKU_ID, @PACK_ID, @QTY, @ACCEPTED_QTY, @REJECTED_QTY, @LOSE_QTY, @BREAKAGE_QTY, @PKG_BREAKAGE_QTY, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_POD_DETAIL = " SELECT  ROWID, WH_ID, POD_ID, LINE_ID, BOL_LINE_ID, DELIVERY_ID, DELIVERY_LINE_ID, STATUS, OWNER_ID, SKU_ID, PACK_ID, QTY, ACCEPTED_QTY, REJECTED_QTY, LOSE_QTY, BREAKAGE_QTY, PKG_BREAKAGE_QTY, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@POD_DETAIL ";
        private string SQL_UPDATE_POD_DETAIL = " UPDATE @_@POD_DETAIL SET WH_ID = @WH_ID, BOL_LINE_ID = @BOL_LINE_ID, DELIVERY_ID = @DELIVERY_ID, DELIVERY_LINE_ID = @DELIVERY_LINE_ID, STATUS = @STATUS, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, PACK_ID = @PACK_ID, QTY = @QTY, ACCEPTED_QTY = @ACCEPTED_QTY, REJECTED_QTY = @REJECTED_QTY, LOSE_QTY = @LOSE_QTY, BREAKAGE_QTY = @BREAKAGE_QTY, PKG_BREAKAGE_QTY = @PKG_BREAKAGE_QTY, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE POD_ID = @POD_ID AND LINE_ID = @LINE_ID  ";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckPodDetailIDUnique(string podID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID), new SqlParameter("@LINE_ID", lineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_POD_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckPodDetailIDUnique(DataBase dataBase, DbTransaction tran, string podID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID), new SqlParameter("@LINE_ID", lineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_POD_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeletePodDetail(List<string> podIDs, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < podIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_POD_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeletePodDetail(List<string> podIDs, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < podIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podIDs[j]), new SqlParameter("@LINE_ID", lineIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_POD_DETAIL.Replace("@_@", whLoginID[i]) + " AND LINE_ID = @LINE_ID ", paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeletePodDetail(DataBase dataBase, DbTransaction tran, List<string> podIDs, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < podIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_POD_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeletePodDetail(DataBase dataBase, DbTransaction tran, List<string> podIDs, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < podIDs.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podIDs[j]), new SqlParameter("@LINE_ID", lineIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_POD_DETAIL.Replace("@_@", whLoginID[i]) + " AND LINE_ID = @LINE_ID ", paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllPodDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllPodDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public PodDetailInfo GetPodDetailByID(string podID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID) + " WHERE POD_ID = @POD_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID), new SqlParameter("@LINE_ID", lineID) };
            PodDetailInfo podDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    podDetailInfo = new PodDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["POD_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, (reader["BOL_LINE_ID"].ToString() != "") ? int.Parse(reader["BOL_LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ACCEPTED_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPTED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_BREAKAGE_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return podDetailInfo;
        }

        public PodDetailInfo GetPodDetailByID(DataBase dataBase, DbTransaction tran, string podID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID) + " WHERE POD_ID = @POD_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@POD_ID", podID), new SqlParameter("@LINE_ID", lineID) };
            PodDetailInfo podDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                podDetailInfo = new PodDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["POD_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, (reader["BOL_LINE_ID"].ToString() != "") ? int.Parse(reader["BOL_LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ACCEPTED_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPTED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_BREAKAGE_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return podDetailInfo;
        }

        public DataSet GetPodDetailByQueryList(List<string> sqlWhere, PodDetailQueryEntity poddetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!poddetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, poddetailQuery.CurrentPage, poddetailQuery.PageSize, poddetailQuery.SortField, poddetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetPodDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, PodDetailQueryEntity poddetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, poddetailQuery.CurrentPage, poddetailQuery.PageSize, poddetailQuery.SortField, poddetailQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<PodDetailInfo> GetPodDetailListByID(string podID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID) + " WHERE POD_ID = @POD_ID ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.POD_ID, podID) };
            List<PodDetailInfo> podDetailInfos = new List<PodDetailInfo>();
            PodDetailInfo podDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    podDetailInfo = new PodDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["POD_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, (reader["BOL_LINE_ID"].ToString() != "") ? int.Parse(reader["BOL_LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ACCEPTED_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPTED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_BREAKAGE_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    podDetailInfos.Add(podDetailInfo);
                }
            }
            return podDetailInfos;
        }

        public List<PodDetailInfo> GetPodDetailListByID(DataBase dataBase, DbTransaction tran, string podID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_POD_DETAIL.Replace("@_@", whLoginID) + " WHERE POD_ID = @POD_ID ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.POD_ID, podID) };
            List<PodDetailInfo> podDetailInfos = new List<PodDetailInfo>();
            PodDetailInfo podDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                podDetailInfo = new PodDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["POD_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, (reader["BOL_LINE_ID"].ToString() != "") ? int.Parse(reader["BOL_LINE_ID"].ToString()) : 0, reader["DELIVERY_ID"].ToString(), (reader["DELIVERY_LINE_ID"].ToString() != "") ? int.Parse(reader["DELIVERY_LINE_ID"].ToString()) : 0, reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ACCEPTED_QTY"].ToString() != "") ? decimal.Parse(reader["ACCEPTED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, (reader["LOSE_QTY"].ToString() != "") ? decimal.Parse(reader["LOSE_QTY"].ToString()) : 0M, (reader["BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["BREAKAGE_QTY"].ToString()) : 0M, (reader["PKG_BREAKAGE_QTY"].ToString() != "") ? decimal.Parse(reader["PKG_BREAKAGE_QTY"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                podDetailInfos.Add(podDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return podDetailInfos;
        }

        public int InsertPodDetail(PodDetailInfo podDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PodDetail_Parameters(podDetailInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_POD_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertPodDetail(DataBase dataBase, DbTransaction tran, PodDetailInfo podDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PodDetail_Parameters(podDetailInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_POD_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_PodDetail_Parameters(PodDetailInfo podDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.POD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.BOL_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.DELIVERY_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DELIVERY_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 20), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ACCEPTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LOSE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.BREAKAGE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PKG_BREAKAGE_QTY, SqlDbType.Decimal, 13), 
                new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30)
             };
            if (!string.IsNullOrEmpty(podDetailInfo.WhID))
            {
                paramArray[0].Value = podDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = podDetailInfo.PodID;
            paramArray[2].Value = podDetailInfo.LineID;
            paramArray[3].Value = podDetailInfo.BolLineID;
            if (!string.IsNullOrEmpty(podDetailInfo.DeliveryID))
            {
                paramArray[4].Value = podDetailInfo.DeliveryID;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = podDetailInfo.DeliveryLineID;
            if (!string.IsNullOrEmpty(podDetailInfo.Status))
            {
                paramArray[6].Value = podDetailInfo.Status;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.OwnerID))
            {
                paramArray[7].Value = podDetailInfo.OwnerID;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.SkuID))
            {
                paramArray[8].Value = podDetailInfo.SkuID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.PackID))
            {
                paramArray[9].Value = podDetailInfo.PackID;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            paramArray[10].Value = podDetailInfo.Qty;
            paramArray[11].Value = podDetailInfo.AcceptedQty;
            paramArray[12].Value = podDetailInfo.RejectedQty;
            paramArray[13].Value = podDetailInfo.LoseQty;
            paramArray[14].Value = podDetailInfo.BreakageQty;
            paramArray[15].Value = podDetailInfo.PkgBreakageQty;
            if (!string.IsNullOrEmpty(podDetailInfo.Udf1))
            {
                paramArray[0x10].Value = podDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf2))
            {
                paramArray[0x11].Value = podDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf3))
            {
                paramArray[0x12].Value = podDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf4))
            {
                paramArray[0x13].Value = podDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf5))
            {
                paramArray[20].Value = podDetailInfo.Udf5;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf6))
            {
                paramArray[0x15].Value = podDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf7))
            {
                paramArray[0x16].Value = podDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf8))
            {
                paramArray[0x17].Value = podDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf9))
            {
                paramArray[0x18].Value = podDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Udf10))
            {
                paramArray[0x19].Value = podDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(podDetailInfo.Remark))
            {
                paramArray[0x1a].Value = podDetailInfo.Remark;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            paramArray[0x1b].Value = podDetailInfo.CreatedBy;
            paramArray[0x1c].Value = podDetailInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdatePodDetail(PodDetailInfo podDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PodDetail_Parameters(podDetailInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_POD_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePodDetail(DataBase dataBase, DbTransaction tran, PodDetailInfo podDetailInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    podDetailInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PodDetail_Parameters(podDetailInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_POD_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

